import sqlite3


def CreateDB(Name):
	conn = sqlite3.connect('%s' % Name + ".dp")
	#conn = sqlite3.connect('example.db')
	c = conn.cursor()
	c.execute('''CREATE TABLE Courses (Id integer, Name text, UnivCourseNo integer)''')
	c.execute('''CREATE TABLE Classes (Id integer, CourseId integer, WeightingSchemeId integer)''')
	c.execute('''CREATE TABLE Students (Id integer, PersonId integer, ClassId integer, FinalGrade integer)''')
	c.execute('''CREATE TABLE Person (Id integer, First text, Last text, UnivId integer, email text)''')
	c.execute('''CREATE TABLE Score (Id integer, CourseScore real, StudentId integer, AssignQuestId integer, Flags blob)''')
	c.execute('''CREATE TABLE Assignments (Id integer, PDF text, revisionId integer)''')
	c.execute('''CREATE TABLE AssignmentClasses (Id integer, ClassId integer, AssignmentId integer, AssignmentType text, WeightingCategory text, IndivQuestScores integer)''')
	c.execute('''CREATE TABLE Questions (Id integer, Notes text)''')
	c.execute('''CREATE TABLE AssignmentQuestions (Id integer, AssignmentId integer, QuestionId integer, NoPts real, QuestionNo integer, IsBonus integer, IsTotal integer)''')
	c.execute('''CREATE TABLE WeightingScheme (Id integer)''')
	c.execute('''CREATE TABLE Weight (Id integer)''')
	conn.commit()
	conn.close()

def OpenDB(Name):
	conn = sqlite3.connect('%s' % Name + ".dp")
	#conn = sqlite3.connect('example.db')
	c = conn.cursor()
	
def AddCourse(Id,Name,UniCoNo):
	addcourse = (Id,'%s' % Name, UniCoNo)
	print addcourse
	#c.execute("INSERT INTO Courses VALUES ('%d' % Id,'%s' % Name,'%d' % UniCoNo)")
	c.execute("INSERT INTO Courses VALUES (?,?,?)", addcourse)
	conn.commit()
	
def AddClass(Id,CourseId,WSchemeId):
	addclass = (Id,CourseId,WSchemeId)
	c.execute("INSERT INTO Courses VALUES (?,?,?)", addclass)
	conn.commit()

def AddStudent(Id,PersonId,ClassId):
	addstudent = (Id,PersonId,ClassId)
	c.execute("INSERT INTO Students (Id, PersonId, ClassId) VALUES (?,?,?)", addstudent)
	conn.commit()

def AddPerson(Id,First,Last,UnivId,email):
	addperson = (Id,'%s' % First, '%s' % Last, UnivId,'%s' % email)
	c.execute("INSERT INTO Person VALUES (?,?,?,?,?)", addperson)
	conn.commit()

def AddScore(Id,CourseScore,StudId,AQId,Flags):
	addscore = (Id,CourseScore, StudId, AQId,Flags)
	c.execute("INSERT INTO Score VALUES (?,?,?,?,?)", addscore)
	conn.commit()

def AddAssignment(Id,PDF):
	addAssi = (Id,"%s" % PDF)
	c.execute("INSERT INTO Assignments (Id,PDF) VALUES (?,?)", addAssi)
	conn.commit()
	
#SELECT * FROM COMPANY ORDER BY SALARY ASC
#SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

'''
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
'''
# ,TableName,WhereCondition,GroupList,HavingCondition,OrderList
def Select(ColumnList,TableName):
	execstr = "SELECT "
	if (len(ColumnList) == 0):
		execstr += "* "
	else:
		execstr+=", ".join(ColumnList)+" " #Add all strings in the list "ColumnList" separated by ", "
		'''for j in range(0,len(ColumnList)-1):
			executionstring += "%s" % ColumnList[j]
			if (j < len(Columnlist)):
				execustionstring += ", "
			else:
				execustionstring += " "'''
	execstr += "FROM " + "%s " % TableName
	if (WhereCondition != ""):
		exectr += "WHERE " + "%s " % WhereCondition
	if (len(GroupList) != 0):
		execstr += "GROUP BY " + ", ".join(GroupList)+" "
	if (HavingCond != ""):
		execstr += "HAVING " + "%s " % HavingCond
	if (len(OrderList) != 0):
		execstr += "ORDER BY " + ", ".join(OrderList)+" "
	execstr=execstr[:-1] #Remove the last Character
	print executionstring;
	
	#c.execute("SELECT * FROM " + "%s" % Tablename + " WHERE symbol=?", Searchlist)
	#return c.fetchall()


	

	
